
package com.scalar.chiptrack.operations.process.dao;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.HashMap;

import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.SQLException;

import com.scalar.ScalarException;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.operations.process.BinSetup;


public class BinSetupDAO {

    
    public static ArrayList loadSubstituableBins (int processStepId) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        
        ArrayList substitutableBinList = new ArrayList();
        
        try 
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.binsetup.loadSubstitutableBins"));
            pstmt.setInt (1, processStepId);
            
            rset = pstmt.executeQuery();

            while (rset.next()) 
            {
                substitutableBinList.add (rset.getString ("Part_Number") + "|" + rset.getString ("Part_Number_To_Substitute"));
            }
        }
        catch (Exception sqle) 
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Substitutable bin information from database", sqle);
        }
        finally 
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return substitutableBinList;
    }
    
    
    public static void storeBinSubstitutions (DBTransaction dbTransaction, int processStepId, HashMap substBins) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean status = false;

        if (substBins == null) 
        {
            return;
        }
        
        try
        {
            if (dbTransaction == null) 
            {
                conn = DataSource.getConnection();
            }
            else 
            {
                conn = dbTransaction.getConnection();
            }
            
			// added by Ravi kanth on 11 Sep 2003
			// remove the old bin substitution information and add the new information
			pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.binsetup.deleteOldSubstitutableBins"));
			pstmt.setInt(1,processStepId);
			pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;

            pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.binsetup.saveSubstitutableBins"));
            Iterator keys = substBins.keySet().iterator();
            Object keyPartNumber = null;
            String[] substPartNumbers = null;
            
            while (keys.hasNext()) 
            {
                keyPartNumber = keys.next();
                substPartNumbers = (String[]) substBins.get (keyPartNumber);
                
                if (substPartNumbers != null) 
                {
                    for (int i=0; i<substPartNumbers.length; i++) 
                    {
                        pstmt.setInt (1, processStepId);
                        pstmt.setString (2, ((String)keyPartNumber).trim());
                        pstmt.setString (3, substPartNumbers[i].trim());
                        
                        pstmt.addBatch();
                    }
                }
            }
            
            pstmt.executeBatch();
            status = true;
        }

        catch(SQLException sql) 
        {
            sql.printStackTrace();
            throw new ScalarException ("Couldnt store Substitutable Bin information", sql);
        }
        finally 
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (status == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if(pstmt != null) {
                try{ pstmt.close(); }catch(Exception e){}
            }
            if(conn != null) {
                try{ conn.close(); }catch(Exception e){}
            }
        }
    }
    

    public static ArrayList loadBins (int processStepId) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        
        ArrayList binList = new ArrayList();
        BinSetup binSetup = null;
        
        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.binsetup.loadBins"));
            pstmt.setInt (1, processStepId);
            
            rset = pstmt.executeQuery();

            while (rset.next()) {
                
                binSetup = new BinSetup (rset.getString ("WSBT_Bin_Number"),
                                          rset.getString ("WSBT_Bin_Type"),
                                          rset.getString ("WSBT_Description"),
                                          rset.getString ("WSBT_Part_Number"),
                                          rset.getFloat ("WSBT_Yield"));
                binSetup.setOldPartNumber(binSetup.getPartNumber());                          
                binList.add (binSetup);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load bin information from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return binList;
    }

	/* 
	 * This loadBins method is overloaded so that the 
	 * Mark_Pack_ProcessStep_ID and FG_ProcessStep_ID are set
	 * for updating the bin set up
	 */
	
	public static ArrayList loadBins (int processStepId, int trackId) throws ScalarException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
        
		ArrayList binList = new ArrayList();
		BinSetup binSetup = null;
        
        try 
        {
			conn = DataSource.getConnection();

			pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.binsetup.loadBins"));
			pstmt.setInt (1, processStepId);
            
			rset = pstmt.executeQuery();

			while (rset.next()) 
			{
				binSetup = new BinSetup (rset.getString ("WSBT_Bin_Number"),
										  rset.getString ("WSBT_Bin_Type"),
										  rset.getString ("WSBT_Description"),
										  rset.getString ("WSBT_Part_Number"),
										  rset.getFloat ("WSBT_Yield"),"-1","-1");
				binSetup.setOldPartNumber(binSetup.getPartNumber());
				binList.add (binSetup);
			}
			
			rset.close();
			pstmt.close();
			pstmt = null;
			
			pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.binsetup.loadBins.markPackFGids"));
			
			for( int i=0 ; i < binList.size() ; i++ )
			{
				BinSetup bin = (BinSetup) binList.get(i);
				if(bin.getBinType().equals("Accept"))
				{
					pstmt.setInt( 1 , trackId );
					pstmt.setString( 2 , bin.getPartNumber() );
					rset = pstmt.executeQuery();
					if(rset.next())
					{
						if ( rset.getString("Mark_Pack_ProcessStep_ID") != null )
						{
							bin.setMark_Pack_ProcessStep_ID( rset.getString("Mark_Pack_ProcessStep_ID") );
						}
						else
						{
							bin.setMark_Pack_ProcessStep_ID( "-2" );
						}
							
						if(rset.getString("FG_ProcessStep_ID") != null)
						{
							bin.setFG_ProcessStep_ID( rset.getString("FG_ProcessStep_ID") );
						}
						else
						{
							bin.setFG_ProcessStep_ID("-2");
						}
						
					}
				}
			}
		}
		catch (Exception sqle) 
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not load bin information from database", sqle);
		}
		finally {
			if (rset != null) {
				try { rset.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}

		return binList;
	}

    
    
    public static void saveBins (DBTransaction dbTransaction, int processStepId, int baseProductId, /*int productIdSeed, */Iterator bins) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;
        ResultSet rset = null;
        
        boolean status = false;

        try{
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
            
            // STEP 1
            // Delete if any rows exist for this process step ID
            pstmt = conn.prepareStatement (SqlQueryMappings.get("operations.process.binsetup.deleteBinsByProcessStepId"));
            pstmt.setInt (1, processStepId);
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;
            
            
            // STEP 2
            // Save Bins
            pstmt = conn.prepareStatement (SqlQueryMappings.get("operations.process.binsetup.insertBins"));
            
            BinSetup binSetup = null;

            while (bins.hasNext()) {
                                
                binSetup = (BinSetup) bins.next();

                if ((binSetup.getBinNo() != null) && (binSetup.getBinType() != null)) {
                               
                    // Store Bin Information
                    pstmt.setInt (1, processStepId);
                    pstmt.setString (2, binSetup.getBinNo());
                    pstmt.setString (3, binSetup.getBinType());                           
                    pstmt.setString (4, binSetup.getBinDescription());
                    pstmt.setString (5, binSetup.getPartNumber());
                    pstmt.setFloat (6, binSetup.getYield());
                    //pstmt.setInt (5, productIdSeed++);
                    pstmt.addBatch();
                }
            }

            pstmt.executeBatch();
            status = true;
        }

        catch(SQLException sql) {
            sql.printStackTrace();
            throw new ScalarException ("Couldnt store Binning informatin", sql);
        }

        finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (status == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if(pstmt != null) {
                try{ pstmt.close(); }catch(Exception e){}
            }
            if(pstmt1 != null) {
                try{ pstmt.close(); }catch(Exception e){}
            }
            if(pstmt2 != null) {
                try{ pstmt.close(); }catch(Exception e){}
            }
            
            if(conn != null) {
                try{ conn.close(); }catch(Exception e){}
            }
        }
    }


	/**
	 * checks whether the part number already exists in the database
	 * @param str
	 */
	public static String checkBins(String str, int processStepId) throws ScalarException 
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		String result = "";
        
		try 
		{
			 conn = DataSource.getConnection();
                         /*Bhaskar added the if condition and placed the else block for 
                         the old code done by Ravikanth on 16 sep 2003*/
                         if ( processStepId == -1 )
                         {
                            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.binsetup.checkBinPartNumbersForKitMaking"));
                            pstmt.setString(1, str);
                         }
                         else 
                         {
                            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.binsetup.checkBinPartNumbers")+"(" + str + ")");
                            pstmt.setInt(1,processStepId);
                         }
                         rset = pstmt.executeQuery();
			 while (rset.next()) 
			 {
            	result = result + rset.getString("WSBT_Part_Number")+",";
			 }
		 }
		 catch (Exception sqle) {
			 sqle.printStackTrace();
			 throw new ScalarException ("Could not load bin information from database", sqle);
		 }
		 finally {
			 if (rset != null) {
				 try { rset.close(); } catch (SQLException ignore) {}
			 }
			 if (pstmt != null) {
				 try { pstmt.close(); } catch (SQLException ignore) {}
			 }
			 if (conn != null) {
				 try { conn.close(); } catch (SQLException ignore) {}
			 }
		 }
		 if( result.length() > 1 )
		 {
			result =  result.substring( 0 , result.length()-1 );
		 }
		 return result;
	}
	
	/*
	 * checks whether the part number already exists in the database with ProcessStepId
	 */
	public static String checkBinsByProcessId( int processStepId,String partNumber) throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		String result = "";
		        
		try 
		{
			 conn = DataSource.getConnection();
			 pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.binsetup.checkBinPartNumbersByProcessStepId"));
			 pstmt.setString(1,partNumber);
			 pstmt.setInt(2,processStepId);
			 rset = pstmt.executeQuery();
			 if (rset.next()) 
			 {
				result = rset.getString("part_number");
			 }
		 }
		 catch (Exception sqle) {
			 sqle.printStackTrace();
			 throw new ScalarException ("Could check bin information from database", sqle);
		 }
		 finally {
			 if (rset != null) {
				 try { rset.close(); } catch (SQLException ignore) {}
			 }
			 if (pstmt != null) {
				 try { pstmt.close(); } catch (SQLException ignore) {}
			 }
			 if (conn != null) {
				 try { conn.close(); } catch (SQLException ignore) {}
			 }
		 }
		 return result;
	}


	/**
	 * @param string
	 * @return
	 */
	public static String checkProductTable(String string) throws ScalarException{
		String result = "";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;		        
		try 
		{
			 conn = DataSource.getConnection();
			 pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.binsetup.checkProductPartNumbersForBinSetup")+" ("+ string +")");
			 rset = pstmt.executeQuery();
			 while (rset.next()) 
			 {
				result = result + rset.getString("part_number")+",";
			 }
		 }
		 catch (Exception sqle) 
		 {
			 sqle.printStackTrace();
			 throw new ScalarException ("Could not check bin information from database", sqle);
		 }
		 finally 
		 {
			 if (rset != null) {
				 try { rset.close(); } catch (SQLException ignore) {}
			 }
			 if (pstmt != null) {
				 try { pstmt.close(); } catch (SQLException ignore) {}
			 }
			 if (conn != null) {
				 try { conn.close(); } catch (SQLException ignore) {}
			 }
		 }
		
		 if( result.length()>0 )
		 {
			return result.substring( 0 , result.length()-1 );
		 }
		 else
		 {
			return result;
		 }		
		 
	}

	/**
	 * @param string
	 * @return String
	 */
	public static String checkBinsInProductForBoardKit(String string) throws ScalarException{
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		String result = "";
        
		try 
		{
			 conn = DataSource.getConnection();
			 pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.binsetup.checkBinPartNumbersInProductInBoardKit")+"(" + string + ")");
			 rset = pstmt.executeQuery();
			 while (rset.next()) 
			 {
				result = result + rset.getString("Part_Number")+",";
			 }
		 }
		 catch (Exception sqle) {
			 sqle.printStackTrace();
			 throw new ScalarException ("Could not load bin information from database", sqle);
		 }
		 finally {
			 if (rset != null) {
				 try { rset.close(); } catch (SQLException ignore) {}
			 }
			 if (pstmt != null) {
				 try { pstmt.close(); } catch (SQLException ignore) {}
			 }
			 if (conn != null) {
				 try { conn.close(); } catch (SQLException ignore) {}
			 }
		 }
		 if( result.length() > 1 )
		 {
			result =  result.substring( 0 , result.length()-1 );
		 }
		 return result;
	}
	
	public static void updateBinSubtitutables( DBTransaction dbTransaction , String newPartNumber , String oldPartNumber ) throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean status = false;

		try 
		{
			if (dbTransaction == null) {
				conn = DataSource.getConnection();
			}
			else 
			{
				conn = dbTransaction.getConnection();
			}
			pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.binsetup.updateBinSubstitutablesPartNumber" ) );
			pstmt.setString( 1 , newPartNumber );
			pstmt.setString( 2 , oldPartNumber );
			pstmt.executeUpdate();
			pstmt.close();
			pstmt= null;
			
			pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.process.binsetup.updateBinSubstitutablesPartNumberToSubstitute" ) );
			pstmt.setString( 1 , newPartNumber );
			pstmt.setString( 2 , oldPartNumber );
			pstmt.executeUpdate();
			status = true;
		
		}
		catch (Exception sqle) 
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not check bin information from database", sqle);
		}
		finally 
		{
			// Commit/Rollback here it selef -- if its not in a manual transaction
			if (dbTransaction == null) {
				try {
					if (conn != null) {
						if (status == true) {
							conn.commit();
						}
						else {
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}

			if(pstmt != null) {
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null) {
				try{ conn.close(); }catch(Exception e){}
			}
		}
	}	
	
}